package com.nbjtjc.safe.mapper.cost;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.nbjtjc.safe.model.cost.CostBudgetItem;
import com.nbjtjc.safe.model.cost.CostBudgetPlan;
import com.nbjtjc.safe.model.cost.CostBudgetSubItem;
import com.nbjtjc.util.MyMapper;
@Mapper
public interface CostBudgetSubItemMapper extends MyMapper<CostBudgetSubItem> {
	@Select("select * from cost_budget_sub_item where cancel = 0 and budget_item_id = #{itemId}")
	List<CostBudgetSubItem> findByItemId(@Param("itemId") int itemId);
	
	List<CostBudgetSubItem> findDetailByItemId(@Param("itemId") int itemId);
	
	@Select("select id from cost_budget_sub_item where cancel = 0 and budget_item_id = #{id}")
	ArrayList<Integer> findIdByItemId(@Param("id") Integer id);
	
	@Update("UPDATE cost_budget_sub_item SET "
			+ "account_sum = (SELECT SUM(IFNULL(account_num,0)) FROM cost_account_record WHERE budget_item_id = #{id})"
			+ ",account_remain = `item_budget`-`account_sum` "
			+ "WHERE id = #{id}")
	int updateAccountSum(@Param("id") int id);
	
	@Update("update cost_budget_sub_item set cancel = 1 where budget_item_id = #{itemId}")
	int cancelByItemId(@Param("itemId")Integer itemId);
	
	@Insert(
			"INSERT INTO  cost_budget_sub_item (item,budget_item_id,item_temp_id) "
			+ "(SELECT a.item AS item,u.item_id AS budget_item_id,a.id AS item_temp_id FROM cost_budget_plan_templet a INNER JOIN"
			+ "	 (SELECT id AS item_id,item_temp_id FROM cost_budget_item WHERE cancel = 0 AND budget_plan_id = #{cbp.id} AND item_temp_id >0) u "
			+ "ON a.`parent_id` = u.item_temp_id WHERE a.cancel = 0)"
			)
	int insertSubItemByTemplet(@Param("cbp")CostBudgetPlan costBudgetPlan);
	

	
	
	@Insert("INSERT INTO cost_budget_month(plan_id,item_id,item_type,`year`,`item_temp_id`) "
			+"SELECT #{cbp.id} AS plan_id,id AS item_id,2 AS item_type,#{cbp.year} AS `year`,`item_temp_id` FROM  cost_budget_sub_item a INNER JOIN "
			+"(SELECT id AS item_id FROM cost_budget_item WHERE cancel = 0 AND budget_plan_id = #{cbp.id}) u ON a.budget_item_id = u.item_id WHERE a.cancel = 0")
	int insertSubItemMonthTemplet(@Param("cbp") CostBudgetPlan costBudgetPlan);
	
	@Insert("INSERT INTO cost_budget_month(plan_id,item_id,item_type,`year`) "
			+"SELECT #{cbp.id} AS plan_id,id AS item_id,2 AS item_type,#{cbp.year} AS `year` FROM  cost_budget_sub_item a INNER JOIN "
			+"(SELECT id AS item_id FROM cost_budget_item WHERE cancel = 0 AND budget_plan_id = #{cbp.id}) u ON a.budget_item_id = u.item_id WHERE a.cancel = 0")
	int insertSubItemMonth(@Param("cbp") CostBudgetPlan costBudgetPlan);


	@Update(" UPDATE cost_budget_sub_item a "
 +" INNER JOIN " 
+"	(SELECT SUM(IFNULL(b.account_num,0)) AS account_num,a.id AS id FROM cost_budget_sub_item a LEFT JOIN cost_account_record b ON a.id = b.budget_item_id "
+"	INNER JOIN cost_budget_item c ON a.budget_item_id = c.id  WHERE c.budget_plan_id = #{planId} AND ( b.`cancel`=0 OR b.`cancel` IS NULL ) and a.`cancel`=0 and c.`cancel`=0 GROUP BY a.id ) u "
+"  ON a.id = u.id "
+" SET  a.account_sum = u.account_num ,a.account_remain= a.`item_budget`-u.account_num")
	int updateAccountSumByPlanId(@Param("planId") int planId);
	
	@Select("select * from cost_budget_sub_item where id = #{id} limit 1")
	CostBudgetSubItem findById(@Param("id") int id);
}